<html>
<head>

<title>Groovy Goodness: Paging Support in Groovy SQL</title>

<script language="javascript" src="scripts/shCore.js"></script> 
<script language="javascript" src="scripts/shLegacy.js"></script> 
<script language="javascript" src="scripts/shBrushJava.js"></script> 
<script language="javascript" src="scripts/shBrushXml.js"></script> 
<script language="javascript" src="scripts/shBrushJScript.js"></script> 
<script language="javascript" src="scripts/shBrushGroovy.js"></script> 
<script language="javascript" src="scripts/shBrushPlain.js"></script> 
<script language="javascript" src="scripts/shBrushBash.js"></script> 
 
<link href="styles/reset.css" rel="stylesheet" type="text/css" />
<link href="styles/shCore.css" rel="stylesheet" type="text/css" />
<link type="text/css" rel="stylesheet" href="styles/shThemeRDark.css"/>
<link href="styles/blog.css" rel="stylesheet" type="text/css" />

</head>
<body>

<a href="index.html">Back to index</a>

<h3 class="post-title">Groovy Goodness: Paging Support in Groovy SQL</h3>

<div class="post">
<p>Groovy has great <a href="http://mrhaki.blogspot.com/2009/10/groovy-goodness-groovy-sql.html">SQL</a> support. We can easily access databases and execute queries and statements. Since Groovy 1.8 we can even use paging when we query for data. The <code>eachRows()</code> and <code>rows()</code> methods have two new parameters to define the offset and maximum results we want to be returned.</p>
<pre class="brush:groovy;highlight:[21,27]">
@Grapes([
    @Grab(group='com.h2database', module='h2', version='1.3.154'),
    @GrabConfig(systemClassLoader = true)
])
import groovy.sql.*

def sql = Sql.newInstance('jdbc:h2:./test', 'sa', '', 'org.h2.Driver')
sql.execute 'drop table if exists languages'
sql.execute '''
    create table languages(
        id integer not null auto_increment,
        name varchar(20) not null,
        primary key(id)
    )
'''

['Groovy', 'Java', 'Scala', 'JRuby', 'Clojure', 'Jython'].each {
    sql.execute "insert into languages(name) values($it)"
}

// Use paging to set offset to 1 and maximum results to 2.
sql.eachRow('select * from languages', 1, 2) { row -&gt;
    def expectedName = (row.id == 1 ? 'Groovy' : 'Java')
    assert row.name == expectedName
}

// Offset is 3 for resulting resultset, maximum results is 3.
def result = sql.rows("select * from languages where name like 'J%'", 3, 3)
assert result.size() == 1
assert result[0].id == 6 && result[0].name == 'Jython'

sql.execute "drop table languages"

sql.close()
</pre
</div>

<script language="javascript"> 
SyntaxHighlighter.config.bloggerMode = true;
SyntaxHighlighter.config.clipboardSwf = 'scripts/clipboard.swf';
SyntaxHighlighter.defaults['first-line'] = 0;
SyntaxHighlighter.defaults['auto-links'] = false;
SyntaxHighlighter.all();
dp.SyntaxHighlighter.HighlightAll('code');
</script>

</body>
</html>